package database

import (
	"fmt"
	"strings"

	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

// GenerateComplexMigrationSteps 生成复杂迁移的多步骤 SQL（在实际 ALTER TABLE 之前执行）
func GenerateComplexMigrationSteps(db *gorm.DB, model interface{}) ([]string, error) {
	var steps []string

	stmt := &gorm.Statement{DB: db}
	if err := stmt.Parse(model); err != nil {
		return nil, fmt.Errorf("解析模型失败: %w", err)
	}

	tableName := stmt.Schema.Table
	if tableName == "" {
		if tn, ok := model.(interface{ TableName() string }); ok {
			tableName = tn.TableName()
		} else {
			return nil, fmt.Errorf("无法获取表名")
		}
	}

	// 检查表是否存在
	exists, err := TableExists(tableName)
	if err != nil || !exists {
		return nil, nil
	}

	// 获取现有表结构
	existingColumns, err := GetTableColumns(db, tableName)
	if err != nil {
		return nil, err
	}

	existingColMap := make(map[string]TableColumnInfo)
	for _, col := range existingColumns {
		existingColMap[col.Field] = col
	}

	// 检查需要多步骤处理的变更（包括类型转换和 NULL -> NOT NULL）
	if stmt.Schema != nil {
		for _, field := range stmt.Schema.Fields {
			colName := field.DBName
			if colName == "" {
				continue
			}

			if existingCol, exists := existingColMap[colName]; exists {
				// 检查是否需要数据转换
				newType := extractFieldTypeFromField(field)
				existingType := normalizeType(existingCol.Type)
				
				if needsDataConversion(newType, existingType) {
					dataMigration := generateDataConversionSQLWithTable(tableName, colName, newType, existingType)
					if dataMigration != "" {
						steps = append(steps, dataMigration)
					}
				}
				
				// 检查是否需要多步骤迁移（NULL -> NOT NULL）
				if needsMultiStepMigration(field, existingCol) {
					fieldSteps := generateMultiStepSQL(tableName, field, existingCol)
					steps = append(steps, fieldSteps...)
				}
			}
		}
	}

	return steps, nil
}

// needsDataConversion 检查是否需要数据转换
func needsDataConversion(newType, existingType string) bool {
	newType = strings.ToUpper(newType)
	existingType = strings.ToUpper(existingType)
	
	// VARCHAR -> INT/数字类型
	if strings.Contains(existingType, "VARCHAR") || strings.Contains(existingType, "CHAR") {
		if strings.Contains(newType, "INT") || strings.Contains(newType, "DECIMAL") || strings.Contains(newType, "FLOAT") || strings.Contains(newType, "DOUBLE") {
			return true
		}
		if strings.Contains(newType, "DATE") || strings.Contains(newType, "TIMESTAMP") {
			return true
		}
	}
	
	// INT -> VARCHAR
	if strings.Contains(existingType, "INT") && strings.Contains(newType, "VARCHAR") {
		return true
	}
	
	return false
}

// generateDataConversionSQLWithTable 生成包含表名的数据转换 SQL
func generateDataConversionSQLWithTable(tableName, columnName, newType, existingType string) string {
	newType = strings.ToUpper(newType)
	existingType = strings.ToUpper(existingType)
	
	var conversionSQL string
	
	// VARCHAR -> INT/BIGINT
	if strings.Contains(existingType, "VARCHAR") || strings.Contains(existingType, "CHAR") {
		if strings.Contains(newType, "INT") {
			if strings.Contains(newType, "UNSIGNED") {
				conversionSQL = fmt.Sprintf(
					"-- 数据转换：VARCHAR -> INT UNSIGNED\n"+
					"UPDATE `%s` SET `%s` = CAST(`%s` AS UNSIGNED) WHERE `%s` REGEXP '^[0-9]+$';\n"+
					"UPDATE `%s` SET `%s` = 0 WHERE `%s` IS NULL OR `%s` NOT REGEXP '^[0-9]+$';",
					tableName, columnName, columnName, columnName,
					tableName, columnName, columnName, columnName,
				)
			} else {
				conversionSQL = fmt.Sprintf(
					"-- 数据转换：VARCHAR -> INT\n"+
					"UPDATE `%s` SET `%s` = CAST(`%s` AS SIGNED) WHERE `%s` REGEXP '^[0-9]+$';\n"+
					"UPDATE `%s` SET `%s` = 0 WHERE `%s` IS NULL OR `%s` NOT REGEXP '^[0-9]+$';",
					tableName, columnName, columnName, columnName,
					tableName, columnName, columnName, columnName,
				)
			}
		} else if strings.Contains(newType, "DECIMAL") || strings.Contains(newType, "FLOAT") || strings.Contains(newType, "DOUBLE") {
			conversionSQL = fmt.Sprintf(
				"-- 数据转换：VARCHAR -> DECIMAL\n"+
				"UPDATE `%s` SET `%s` = CAST(`%s` AS DECIMAL(10,2)) WHERE `%s` REGEXP '^[0-9]+(\\.[0-9]+)?$';\n"+
				"UPDATE `%s` SET `%s` = 0.0 WHERE `%s` IS NULL OR `%s` NOT REGEXP '^[0-9]+(\\.[0-9]+)?$';",
				tableName, columnName, columnName, columnName,
				tableName, columnName, columnName, columnName,
			)
		} else if strings.Contains(newType, "DATE") || strings.Contains(newType, "TIMESTAMP") {
			conversionSQL = fmt.Sprintf(
				"-- 数据转换：VARCHAR -> DATE/TIMESTAMP（请根据实际日期格式调整）\n"+
				"UPDATE `%s` SET `%s` = STR_TO_DATE(`%s`, '%%Y-%%m-%%d') WHERE `%s` IS NOT NULL AND `%s` REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}';",
				tableName, columnName, columnName, columnName, columnName,
			)
		}
	}
	
	// INT -> VARCHAR
	if strings.Contains(existingType, "INT") && strings.Contains(newType, "VARCHAR") {
		conversionSQL = fmt.Sprintf(
			"-- 数据转换：INT -> VARCHAR\n"+
			"UPDATE `%s` SET `%s` = CAST(`%s` AS CHAR) WHERE `%s` IS NOT NULL;",
			tableName, columnName, columnName, columnName,
		)
	}
	
	return conversionSQL
}

// needsMultiStepMigration 检查是否需要多步骤迁移
func needsMultiStepMigration(field *schema.Field, existingCol TableColumnInfo) bool {
	// 场景1: 从 NULL 改为 NOT NULL（需要先设置默认值）
	newNullable := !strings.Contains(field.Tag.Get("gorm"), "not null")
	existingNullable := existingCol.Null == "YES"
	
	if !newNullable && existingNullable {
		// 需要检查是否有默认值
		hasDefault := field.TagSettings["DEFAULT"] != "" || field.HasDefaultValue
		if !hasDefault {
			return true
		}
	}

	return false
}

// generateMultiStepSQL 生成多步骤迁移 SQL
func generateMultiStepSQL(tableName string, field *schema.Field, existingCol TableColumnInfo) []string {
	var steps []string

	// 场景1: NULL -> NOT NULL，没有默认值
	newNullable := !strings.Contains(field.Tag.Get("gorm"), "not null")
	existingNullable := existingCol.Null == "YES"
	
	if !newNullable && existingNullable {
		hasDefault := field.TagSettings["DEFAULT"] != "" || field.HasDefaultValue
		if !hasDefault {
			// 步骤1: 先为 NULL 值设置默认值（在 ALTER TABLE 之前执行）
			defaultValue := getDefaultValueForType(field)
			steps = append(steps, fmt.Sprintf(
				"-- 为 NULL 值设置默认值，以便改为 NOT NULL\nUPDATE `%s` SET `%s` = %s WHERE `%s` IS NULL;",
				tableName, field.DBName, defaultValue, field.DBName,
			))
		}
	}

	return steps
}

// getDefaultValueForType 根据字段类型获取默认值
func getDefaultValueForType(field *schema.Field) string {
	dataType := ""
	if field.TagSettings["TYPE"] != "" {
		dataType = field.TagSettings["TYPE"]
	} else if field.DataType != "" {
		dataType = string(field.DataType)
	}

	dataTypeLower := strings.ToLower(dataType)
	
	switch {
	case strings.Contains(dataTypeLower, "int"):
		return "0"
	case strings.Contains(dataTypeLower, "varchar") || strings.Contains(dataTypeLower, "text"):
		return "''"
	case strings.Contains(dataTypeLower, "timestamp") || strings.Contains(dataTypeLower, "datetime"):
		return "CURRENT_TIMESTAMP"
	case strings.Contains(dataTypeLower, "float") || strings.Contains(dataTypeLower, "double"):
		return "0.0"
	case strings.Contains(dataTypeLower, "bool") || strings.Contains(dataTypeLower, "tinyint(1)"):
		return "0"
	default:
		return "''"
	}
}

